#!/bin/bash

## MySQL命令路径
mysql_exe="mysql"
## MySQL服务器
mysql_host="127.0.0.1"
## MySQL端口
mysql_port=3306
## MySQL账号
mysql_user="mysql_admin"
## MySQL密码
mysql_password=""
## 循环检测次数
check_loops=1000
## 循环间隔时间(以秒为单位)
sleep_seconds=1
## 指定SQL语句保留长度,超过长度的SQL会被截断,-1表示保留全部SQL/
sql_length=300
## 输出结果排序方式,1为按照SQL执行时间排序，0为按照SQL语句排序。
sort_by=1

current_active_process_count=0
current_active_tran_count=0
current_uncommit_tran_count=0
cureent_block_tran_count=0
tmp_log="${mysql_host}_${mysql_port}_tmp.log"
check_time_str=`date '+%Y-%m-%d %H:%M:%S'`


function echo_info()
{
    message=$1
    echo -e "\033[;37;32m ${message} \033[0m"
}


function echo_error()
{
    message=$1
    echo -e "\033[;37;31m ${message} \033[0m"
}


function check_processlist()
{
    if [ $sql_length -eq -1 ]
    then
        sql_text=`cat <<EOF
SELECT
RPAD(ID,20,' ') AS ID,
RPAD(USER,20,' ') AS USER,
RPAD(HOST,20,' ') AS HOST,
RPAD(IFNULL(DB,''),20,' ') AS DB,
RPAD(COMMAND,20,' ') AS COMMAND,
RPAD(TIME,20,' ') AS TIME,
RPAD(STATE,20,' ') AS STATE,
replace(replace(replace(replace(replace(INFO,'\r',' '),'\n',' '),'      ',' '),'    ',' '),'  ',' ') AS SQL_TEXT,
char_length(replace(replace(replace(replace(replace(INFO,'\r',' '),'\n',' '),'      ',' '),'    ',' '),'  ',' ')) AS SQL_LENGTH
FROM information_schema.processlist
WHERE COMMAND NOT IN('sleep')
AND ID<>CONNECTION_ID()
AND USER NOT IN ('system user','replication')
EOF`
    else
        sql_text=`cat <<EOF
SELECT
RPAD(ID,20,' ') AS ID,
RPAD(USER,20,' ') AS USER,
RPAD(HOST,20,' ') AS HOST,
RPAD(IFNULL(DB,''),20,' ') AS DB,
RPAD(COMMAND,20,' ') AS COMMAND,
RPAD(TIME,20,' ') AS TIME,
RPAD(STATE,20,' ') AS STATE,
substring(replace(replace(replace(replace(replace(INFO,'\r',' '),'\n',' '),'      ',' '),'    ',' '),'  ',' '),1,${sql_length}) AS SQL_TEXT,
char_length(replace(replace(replace(replace(replace(INFO,'\r',' '),'\n',' '),'      ',' '),'    ',' '),'  ',' ')) AS SQL_LENGTH
FROM information_schema.processlist
WHERE COMMAND NOT IN('sleep')
AND ID<>CONNECTION_ID()
AND USER NOT IN ('system user','replication')
EOF`
    fi

    if [ $sort_by -eq 1 ]
    then
        sql_text="${sql_text} ORDER BY TIME DESC"
    else
        sql_text="${sql_text} ORDER BY SQL_TEXT DESC"
    fi
     
    tmp_log="logs/${mysql_host}_${mysql_port}_tmp.log"
    ($mysql_exe \
    --host="${mysql_host}"  \
    --port=$mysql_port \
    --user="${mysql_user}"  \
    --password="${mysql_password}" \
    --execute="${sql_text}" > "${tmp_log}") \
    1>/dev/null 2>&1

    if [ $? -ne 0 ]; then
        echo_error "获取活跃线程失败,请检查参数配置是否正确和MySQL实例是否正常。"
        show_usage
        exit 1
    fi

    row_items=`cat "${tmp_log}"|wc -l`;
    if [ $row_items -gt 1 ]
    then
        row_items=$(($row_items-1))
    else
        row_items=0;
    fi

    current_active_process_count=${row_items}
    echo "## ${check_time_str}活跃进程:${row_items}" >> "logs/${mysql_host}_${mysql_port}_process_info.log";
    echo "## ${check_time_str}活跃进程:${row_items}" >> "logs/${mysql_host}_${mysql_port}_process_detail.log";
    cat "${tmp_log}" >> "logs/${mysql_host}_${mysql_port}_process_detail.log";
}

function check_active_transaction(){
    sql_text=`cat <<EOF
SELECT
p.ID AS conn_id,
p.USER AS login_user,
p.HOST AS login_host,
p.DB AS database_name,
p.TIME AS trx_sleep_seconds,
TIME_TO_SEC(TIMEDIFF(NOW(),t.trx_started)) AS trx_open_seconds,
t.trx_started,
t.trx_isolation_level,
t.trx_tables_locked,
t.trx_rows_locked,
t.trx_state,
p.COMMAND AS process_state,
esc.sql_text AS query_sql
FROM information_schema.INNODB_TRX t
INNER JOIN information_schema.PROCESSLIST p
ON t.trx_mysql_thread_id=p.id
LEFT JOIN performance_schema.threads th
ON t.trx_mysql_thread_id = th.processlist_id
LEFT JOIN performance_schema.events_statements_current esc
ON esc.thread_id = th.thread_id
WHERE t.trx_state='RUNNING'
ORDER BY t.trx_started ASC;
EOF`
    tmp_log="logs/${mysql_host}_${mysql_port}_tmp.log"
    ($mysql_exe \
    --host="${mysql_host}"  \
    --port=$mysql_port \
    --user="${mysql_user}"  \
    --password="${mysql_password}" \
    --execute="${sql_text}" > "${tmp_log}") \
    1>/dev/null 2>&1

    if [ $? -ne 0 ]; then
        echo_error "获取活跃失败,请检查参数配置是否正确和MySQL实例是否正常。"
        show_usage
        exit 1
    fi

    row_items=`cat "${tmp_log}" |wc -l`;
    if [ $row_items -gt 1 ]
    then
        row_items=$(($row_items-1))
    else
        row_items=0;
    fi

    current_active_tran_count=${row_items}
    echo "## ${check_time_str}活跃事务:${row_items}" >> "logs/${mysql_host}_${mysql_port}_tran_info.log";
    echo "## ${check_time_str}活跃事务:${row_items}" >> "logs/${mysql_host}_${mysql_port}_tran_detail.log";
    cat "${tmp_log}" >> "logs/${mysql_host}_${mysql_port}_tran_detail.log";

}

function check_transaction_lock(){
    sql_text=`cat <<EOF
SELECT
r.trx_id waiting_trx_id,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_query blocking_query ,
b.trx_mysql_thread_id blocking_thread,
b.trx_started,
b.trx_wait_started
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id
EOF`
     tmp_log="logs/${mysql_host}_${mysql_port}_tmp.log"
    ($mysql_exe \
    --host="${mysql_host}"  \
    --port=$mysql_port \
    --user="${mysql_user}"  \
    --password="${mysql_password}" \
    --execute="${sql_text}" > "${tmp_log}") \
    1>/dev/null 2>&1

    if [ $? -ne 0 ]; then
        echo_error "获取事务锁信息失败,请检查参数配置是否正确和MySQL实例是否正常。"
        show_usage
        exit 1
    fi

    row_items=`cat "${tmp_log}"|wc -l`;
    if [ $row_items -gt 1 ]
    then
        row_items=$(($row_items-1))
    else
        row_items=0;
    fi

    cureent_block_tran_count=${row_items}
    echo "## ${check_time_str}事务阻塞:${row_items}" >> "logs/${mysql_host}_${mysql_port}_lock_info.log";
    echo "## ${check_time_str}事务阻塞:${row_items}" >> "logs/${mysql_host}_${mysql_port}_lock_detail.log";
    cat "${tmp_log}" >> "logs/${mysql_host}_${mysql_port}_lock_detail.log";

}

function check_uncommit_transaction(){
    sql_text=`cat <<EOF
SELECT
p.ID,
p.USER,
p.HOST,
p.DB,
p.TIME,
t.trx_started,
TIMESTAMPDIFF(second,t.trx_started,now()) as trx_seconds,
t.trx_isolation_level,
t.trx_tables_locked,
t.trx_rows_locked,
t.trx_state,
esc.sql_text AS query_sql,
p.COMMAND AS process_state
FROM information_schema.INNODB_TRX t
INNER JOIN information_schema.PROCESSLIST p
ON t.trx_mysql_thread_id=p.id
LEFT JOIN performance_schema.threads th
ON t.trx_mysql_thread_id = th.processlist_id
LEFT JOIN performance_schema.events_statements_current esc
ON esc.thread_id = th.thread_id
WHERE t.trx_state='RUNNING'
AND p.COMMAND='Sleep'
AND p.TIME > 1
ORDER BY t.trx_started ASC;
EOF`
    tmp_log="logs/${mysql_host}_${mysql_port}_tmp.log"
    ($mysql_exe \
    --host="${mysql_host}"  \
    --port=$mysql_port \
    --user="${mysql_user}"  \
    --password="${mysql_password}" \
    --execute="${sql_text}" > "${tmp_log}") \
    1>/dev/null 2>&1

    if [ $? -ne 0 ]; then
        echo_error "获取未提交事务信息失败,请检查参数配置是否正确和MySQL实例是否正常。"
        show_usage
        exit 1
    fi

    row_items=`cat "${tmp_log}" | wc -l`;
    if [ $row_items -gt 1 ]
    then
        row_items=$(($row_items-1))
    else
        row_items=0;
    fi

    cureent_block_tran_count=${row_items}
    echo "## ${check_time_str}未提交事务:${row_items}" >> "logs/${mysql_host}_${mysql_port}_uncommit_info.log";
    echo "## ${check_time_str}未提交事务:${row_items}" >> "logs/${mysql_host}_${mysql_port}_uncommit_detail.log";
    cat "${tmp_log}" >> "logs/${mysql_host}_${mysql_port}_uncommit_detail.log";

}

function loop_check_process(){
    for((i=1;i<=$check_loops;i++));
    do
        if [ $(( $i % 10 )) -eq 1 ]
        then
            printf "|%20s|%20s|%20s|%20s|%20s|\n" "check_time" "active_process_count" "active_tran_count" "uncommit_tran_count" "block_tran_count"
        fi
        check_time_str=`date '+%Y-%m-%d %H:%M:%S'`
        check_processlist
        check_active_transaction
        check_transaction_lock
        check_uncommit_transaction
        printf "|%20s|%20s|%20s|%20s|%20s|\n" "${check_time_str}" $current_active_process_count $current_active_tran_count $current_uncommit_tran_count $cureent_block_tran_count
        sleep ${sleep_seconds};

    done
}

function show_usage()
{
    echo_info "#************************************************************************#"
    echo_info "用途:"
    echo_info "循环抓取MySQL活跃线程/活跃事务/事务阻塞等信息并保存于文本中方便查阅。"
    echo_info ""
    echo_info "参数:"
    echo_info "--mysql_host: MySQL地址,默认为${mysql_host}。"
    echo_info "--mysql_port: MySQL端口,默认${mysql_port}。"
    echo_info "--mysql_user: MySQL账号,默认为${mysql_user}。"
    echo_info "--mysql_password: MySQL账号,默认为${mysql_password}。"
    echo_info "--sql_length: SQL语句保留长度,默认为${sql_length},-1表示保留有全部SQL。"
    echo_info "--sort_by: 活跃进程显示排序方式,默认为${sort_by},1表示按照查询时间排序，2表示按照查询SQL排序。"
    echo_info "--check_loops: 循环抓取次数,默认为${check_loops}。"
    echo_info "--sleep_seconds: 循环抓取间隔(单位秒),默认为${sleep_seconds}。"
    echo_info ""
    echo_info "example:"
    echo_info "sh mysql_process_monitor.sh"
    echo_info "sh mysql_process_monitor.sh --help"
    echo_info "sh mysql_process_monitor.sh --mysql_host='127.0.0.1' --mysql_port=3306  --mysql_user='admin' --mysql_password='admin@123' --sort_by=2 --sql_length=-1 --check_loops=10000"
    echo_info "#************************************************************************#"
}


function show_paras()
{
    echo_info "#************************************************************************#"
    echo_info "当前参数:"
    echo_info "--mysql_host: ${mysql_host}"
    echo_info "--mysql_port: ${mysql_port}"
    echo_info "--mysql_user: ${mysql_user}"
    echo_info "--mysql_password: ${mysql_password}"
    echo_info "--sql_length: ${sql_length}"
    echo_info "--sort_by: ${sort_by}"
    echo_info "--check_loops: ${check_loops}"
    echo_info "--sleep_seconds: ${sleep_seconds}"
    echo_info "#************************************************************************#"
}

## 参数设置
opt_temp=`getopt -o h --long help,mysql_host::,mysql_port::,mysql_user::,mysql_password::,sql_length::,sort_by::,sleep_seconds::,check_loops:: -- "$@"`
if [ $? != 0 ]
then
    echo_error "参数解析失败!"
    show_usage
    exit 1
fi

eval set -- "$opt_temp"
while true ; do
    case "$1" in
        --help)
            show_usage ; exit 1 ;;
        --mysql_host)
            mysql_host=$2 ; shift 2 ;;
        --mysql_port)
            mysql_port=$2 ; shift 2 ;;
        --mysql_user)
            mysql_user=$2 ; shift 2 ;;
        --mysql_password)
            mysql_password=$2 ; shift 2 ;;
        --sql_length)
            sql_length=$2 ; shift 2 ;;
        --sort_by)
            sort_by=$2 ; shift 2 ;;
        --sleep_seconds)
            sleep_seconds=$2 ; shift 2 ;;
        --check_loops)
            check_loops=$2 ; shift 2 ;;
        --ignore)
            IGNRFLAG="ignr" ; shift
            ;;
        --) shift ; break ;;
        *) echo_error "参数解析失败!" ; exit 1 ;;
    esac
done

if [[ "${mysql_host}" == "" ]]
then
    echo_error "请输入MySQL地址"
    show_usage
    exit 1
fi

if [[ "${mysql_port}" == "" ]]
then
    echo_error "请输入MySQL端口"
    show_usage
    exit 1
fi

if [[ "${mysql_user}" == "" ]]
then
    echo_error "请输入MySQL用户"
    show_usage
    exit 1
fi

if [[ "${mysql_password}" == "" ]]
then
    echo_error "请输入MySQL密码"
    show_usage
    exit 1
fi

mkdir -p logs
show_paras
loop_check_process